/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2 
PILOT: SCRAP 
DATE: 7/22/2022 

PROJECT DESCRIPTION: 
Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

DETAILS (This Syntax): 
Identify clinics with any use of the SDH screening tool
Jorge's steps: ASCEND.
Limit to clinics that have at least one facility
	1. have 1+ SDH screeen in any of: food, housing, transportation, or financial resource strain
	active by 7/24/2016 (Jorge) 7/1/2016 (Study period)- check if any discrepancy.
	still active on or after 2/28/2020
	2. Have PC encounters in study period by potential study patients

	Are primary clinic for at least one study patient

INPUT DATA: 
SCRAP.SCRAP_SDH_SCREENING
SCRAP.SCRAP_encounters

OUTPUT DATA:
SCRAP.SDH_SCREENING_3_domains
SCRAP.SCRAP_SDH 
SCRAP.ALL_SDH_facilities
SCRAP.SCRAP_potential_cohort_PC_enc

SECTIONS:
A. Identify all facilities at potential 'clinics'
	>SCRAP.ASCEND_SDH_SCREENING_3_domains
	1. all facilityids in ASCEND.ASCEND_AIM1_8_SDH_screening_2020 with 
		DOMAIN in 'Housing Instability','Food Insecurity','Transportation'
	2. link to delivery site id
	3. create list of all facilityids at delivery sites in A.2 
	4. pull first and last encounter date for facilities in A.3,
	limit to clinics active before 7/1/2016 and after 2/28/2020
		>SCRAP.SDH_facilities_temp
B. Pull ASCEND encounters at facilities in SCRAP_SDH_facilities_temp
	where age_at_encounter_date 23-70, level_of_service=PC code,

	1.Limit to PC providers
		-link to provider NPI taxonomy types
		-identify PC providers (Ascend provider_types, supplement with NPI if provider_type=' '
		-delete non_PC visits

*****************************************************/
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";

/****************************************************
A. Identify all facilities at potential 'clinics'
*****************************************************/

/*1. all facilityids in ASCEND.ASCEND_AIM1_8_SDH_screening_2020 with */
/*		DOMAIN in 'Housing Instability','Food Insecurity','Transportation'*/

data SCRAP_SDH_SCREENING_3_domains (drop=delivery_site_id);
set SCRAP.SCRAP_SDH_SCREENING;
where DOMAIN in('Housing Instability','Food Insecurity','Transportation');
run;

proc sql;
create table scrap.SCRAP_SDH_SCREENING_3_domains as
select distinct f.delivery_site_id,
sdh.*
from SCRAP_SDH_SCREENING_3_domains sdh
	left join ochin.facility_crosswalk f
	on f.facilityid=sdh.facilityid;
quit;

proc sql;
create table SDH_dept as
select distinct 
facilityid,
max(DOMAIN='Housing Instability') as Housing_screen,
max(DOMAIN='Food Insecurity') as Food_screen,
max(DOMAIN='Transportation') as Transportation_screen
from SCRAP.SCRAP_SDH_SCREENING_3_domains
group by facilityid;


/*	2. link to delivery site id*/
proc sql;
create table SDH_clinic_facility_xwalk as 
select distinct
f.delivery_site_id,
	d.facilityid,
	f.facility_type
	from sdh_dept d left join ochin.facility_crosswalk f
	on f.facilityid=d.facilityid;


/*	3. create list of all facilityids at delivery sites in A.2 */
proc sql;
create table SDH_clinic_facility_xwalk2 as
select distinct
delivery_site_id,
	   facilityid,
	   facility_type
from ochin.facility_crosswalk
where delivery_site_id in (select distinct delivery_site_id from 
SDH_clinic_facility_xwalk where delivery_site_id^=' ')
	order by delivery_site_id, facilityid;
	   
proc sql;
select count(distinct delivery_site_id) as clinics, 
count(distinct facilityid) as dept from SDH_clinic_facility_xwalk2;

/*limit to 1 record per facility*/
data SDH_clinic_facility_xwalk3;
set SDH_clinic_facility_xwalk2;
by delivery_site_id  facilityid;
if last.facilityid then output;
run;


/*	4. pull first and last encounter date for facilities in A.4,*/
/*	limit to clinics active before 7/1/2016 and after 2/28/2020*/
/*		>SCRAP.ALL_SDH_facilities*/;

proc sql;
create table facility_enc_dates as
select distinct 
x.delivery_site_id,
min(e.sas_admit_date) as first_enc format date9.,
max(e.sas_admit_date) as last_enc format date9.
from ochin.encounter e inner join sdh_clinic_facility_xwalk3 x
on e.facilityid=x.facilityid
where e.enc_type in('AV','TH') and e.providerid^=' ' and 
e.level_of_service^=' '
group by 
x.delivery_site_id;

proc sql;
create table SCRAP_SDH_facilities_temp as
select x.delivery_site_id,
	   x.facilityid,
	   x.facility_type,
	   s.food_screen,
	   s.housing_screen,
	   s.transportation_screen,
	   d.first_enc as first_clinic_enc format date9.,
	   d.last_enc as last_clinic_enc format date9.
from SDH_clinic_facility_xwalk2 x 
	left join facility_enc_dates d
	on x.delivery_site_id=d.delivery_site_id
left join sdh_dept s on 
	x.facilityid=s.facilityid
	where d.first_enc<'01JUL2016'd and
	d.last_enc>'28FEB2020'd;


/********************************************************************
B. Pull ASCEND encounters at facilities in SCRAP_SDH_facilities_temp
	where age_at_encounter_date 23-70, level_of_service=PC code,
	and providerid^=' ' and date in study period
*********************************************************************/
proc sql;
create table ascend_enc as
select * 
from SCRAP.SCRAP_encounters e
inner join SCRAP_SDH_facilities_temp f
on e.facilityid=f.facilityid
and e.providerid^=' ' and
e.level_of_service in("99245", "99244", "99243", "99242", "99241",
"99215", "99214", "99213", "99212", 
"99205", "99204", "99203", "99202", "99201", 
"99385", "99386", "99387", "99395", "99396", "99397",
"G0402", /*Initial preventive Medicare physical exam */
"G0438", /*Annual wellness visit (Medicare first visit)*/
"G0439",/* Annual wellness visit (subsequent visits);*/
"99381","99382","99383","99384","99381","99385","99386","99387" /*initial preventative */
);

data ascend_enc2;
set ascend_enc;
;if contact_date<'01JUL2016'd then delete;
if contact_date>'29FEB2020'd then delete;
run;


/*	1.Limit to PC providers*/
/*		-link to provider NPI taxonomy types*/
/*		-identify PC providers (Ascend provider_types, supplement with NPI if provider_type=' '*/
/*		-delete non_PC visits*/

proc sql;
create table providers as 
select * from ochin.provider_crosswalk
where providerid in (select distinct providerid from ASCEND_ENC2);

data providers2;
set providers;
rename npi_primary_taxonomy_classificat=npi_primary_tx_class;
rename npi_primary_taxonomy_specializat=npi_primary_tx_specialty;
rename npi_primary_taxonomy_grouping=npi_primary_group;
PC_provider=provider_type in
("Locum Tenans", "Osteopath", "Physician", "Nurse Practitioner",
"Physician Assistant", "Adult Nurse Practitioner", "Locum Physician");
run;

data providers3;
set providers2;
where PC_provider=1 or npi_primary_tx_class^=' ';
run;

data PC_providers (keep=providerid provider_type npi_primary_group
npi_primary_tx_class PC_provider PC_provider2);
set providers3;
PC_provider2=PC_provider;
if PC_provider=0 then do;
	if npi_primary_group='Allopathic & Osteopathic Physicians'
		and npi_primary_tx_class^in('Anesthesiology','Medical Genetics',
	'Neuromusculoskeletal Medicine & OMM','Nuclear Medicine',
	'Ophthalmology','Orthopaedic Surgery','Otolaryngology','Pathology',
	'Pediatrics','Physical Medicine & Rehabilitation','Plastic Surgery',
	'Psychiatry & Neurology','Surgery','Urology') then PC_provider2=1;
	if NPI_primary_group='Physician Assistants & Advanced Practice Nursing Providers'
		then PC_provider2=1;
end;
if PC_provider2=0 then delete;
run;


data scrap.SCRAP_PC_providers ;
set PC_providers;
rename PC_Provider=SCRAP_PC_prov;
rename PC_Provider2=ASCEND_PC_prov;
run;

proc sql;
create table ascend_enc3 as
select e.*,
p.SCRAP_PC_prov,
p.ASCEND_PC_prov 
from ascend_enc2 e left join scrap.SCRAP_PC_providers p
on e.providerid=p.providerid
where p.ascend_PC_prov=1;


data SCRAP.SCRAP_potential_cohort_PC_enc;
set ascend_enc3;
run;

